package main.reports;

import main.Link.LinkArray;
import main.Link.SQLLink;
import main.SQL.MsSQLConnect;
import main.Trees.ProjectNode;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPalette;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.swing.*;
import javax.swing.filechooser.FileFilter;
import javax.swing.filechooser.FileNameExtensionFilter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * Created by s_shmakov on 19.06.14.
 */
public class ReportOneExtend extends Thread{

    private HashMap<String, CellStyle> styles;
    private HashMap<String, Double> allValuesSMR, allValuesSmet, allValuesWork, allValuesSubBlock, allValuesBlock, allValuesSector, allValuesSection, allValuesSetup;
    private HashMap<String, String> hashWorkType, hashSection, setup, hashSector, hashBlock, hashSubBlock, hashSmet, hashSMR, taskType;
    private HashMap<String, Taska> hashWork;
    private HashMap<String, HashMap<String, String>> sectorBlock, blockSubBlock;
//    private HashMap<String, HashSet<String>> blockSmet, SmetSMR;
    private HashMap<String, ArrayList<String>> blockWork, subBlockWork;
    private ProjectNode curProject;
    private String pathXLSFile, sectionSelect, type;
    private Connection connSQL;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private int rownum, curReport;
    private ArrayList<SQLLink> linkArray;
    private HashMap<Integer, SQLLink> sqlList;
    private HashMap<String, ArrayList<Integer>> taskSQL;
    private ArrayList<String> workType;
    private Row row;
    private Cell cell;
    private LinkArray linkArrayStrange;
    private BusyDialog busyDlg;
    private int sheetCount;

    public ReportOneExtend(ProjectNode curProject, Connection connSQL, String sectionSelect, String type) {
        this.curProject = curProject;
        this.connSQL = connSQL;
        this.sectionSelect = sectionSelect;
        this.type = type;
    }

    //region Передача и установка входных параметров, запуск FileChooser'a
    public void run() {
        JFileChooser fileChooser = new JFileChooser();
        fileChooser.setDialogTitle("Выберите путь для сохранения файла");
        fileChooser.setApproveButtonText("Сохранить");
        File fle = new File("РМО - " + curProject.getShortName().replace("/", "-").replace("\\", "-") + " - " + sectionSelect.replace("/", "-").replace("\\", "-"));
        fileChooser.setSelectedFile(fle);

        FileFilter filter = new FileNameExtensionFilter("Файлы Excel - XLS", "xls");
        fileChooser.setFileFilter(filter);
        String homeDir = new File("").getAbsolutePath();
        fileChooser.setCurrentDirectory(new File(homeDir));
        if (fileChooser.showSaveDialog(null) == JFileChooser.APPROVE_OPTION) {
            pathXLSFile = fileChooser.getSelectedFile().getAbsolutePath();
            generateXLS();
        }
    }
    //endregion

    //region Открытие/закрытие соединения с БД. Цикличная генерация отчетов
    private void generateXLS() {

        busyDlg = new BusyDialog("Экспорт матричных отчётов", "Выполняется SQL-запрос");
        busyDlg.start();

        busyDlg.setDescription("Загрузка таблицы связей проекта");
        linkArrayStrange = new LinkArray(curProject);
        linkArrayStrange.downloadFromSQL();
        linkArray = linkArrayStrange.getLinkArray();

        connSQL = MsSQLConnect.ConnectDB("Factage");

        busyDlg.setDescription("Выполенние SQL-запроса");
        fillRows();
        toHash();
//        for (int i = 1; i < 4; i++) {
//            curReport = i;
//            createReport();
//        }
        curReport = 1;
        sheetCount = 1;
        busyDlg.setDescription("Создание отчёта");
        createReport();

        try {
            connSQL.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        busyDlg.setDescription("Готово");
        busyDlg.stopBusy();

    }
    //endregion

    //region Вызов хранимой процедуры и заполнение массивов данных
    private void fillRows() {
        setup = new HashMap<>();
        sectorBlock = new HashMap<>();
        blockSubBlock = new HashMap<>();
        blockWork = new HashMap<>();
        subBlockWork = new HashMap<>();
        allValuesSMR = new HashMap<>();
        allValuesWork = new HashMap<>();
        allValuesSubBlock = new HashMap<>();
        allValuesBlock = new HashMap<>();
        allValuesSector = new HashMap<>();
        allValuesSection = new HashMap<>();
        allValuesSetup = new HashMap<>();
        hashSection = new HashMap<>();
        hashSector = new HashMap<>();
        hashSubBlock = new HashMap<>();
        hashWork = new HashMap<>();
        hashSMR = new HashMap<>();
        hashWorkType = new HashMap<>();
        taskType = new HashMap<>();

        try {
            CallableStatement stmt;
            stmt = connSQL.prepareCall("{call getReportDataSize( ?, ? )}");
            stmt.setString(1, curProject.getDbName());
            stmt.setInt(2, curProject.getId());
            ResultSet rs;

            stmt = connSQL.prepareCall("{call getReportDataFilterNew( ?, ?, ? )}");

            stmt.setString(1, curProject.getDbName());
            stmt.setInt(2, curProject.getId());
            stmt.setString(3, sectionSelect);

            rs = stmt.executeQuery();

            busyDlg.setDescription("Заполнение массива данных");
            HashMap<String, String> tempBlock, tempSubBlock;
            String project, section, sector, block, subBlock, task, actCode, pvCode, taskName;
            while (rs.next()) {
                //region Создание массивов списка подблоков, блоков, секторов, видов работ
                project = rs.getString("proj_name");
                section = rs.getString("section_code_desc");
                sector = rs.getString("sector_code_desc");
                block = rs.getString("block_code_desc");
                subBlock = rs.getString("subblock_code_descr");
                task = rs.getString("task_code");
                pvCode = rs.getString("act_code_value");
                taskName = rs.getString("task_name");

                actCode = rs.getString("act_code_npz_descr");

                taskType.put(task, actCode);
                
                setup.put(section, section);
                hashSector.put(sector, sector);
                hashSubBlock.put(subBlock, subBlock);
                hashWork.put(task, new Taska(taskName, task, pvCode));
                hashWorkType.put(actCode, actCode);
                //endregion

                tempBlock = new HashMap<>();
                tempSubBlock = new HashMap<>();

                //region Создание связей Подблок-Блок / Сектор-Блок
                if (subBlockWork.get(subBlock) != null) {
                    subBlockWork.get(subBlock).add(task);
                } else {
                    ArrayList<String> tempSubBlockWork = new ArrayList<>();
                    tempSubBlockWork.add(task);
                    subBlockWork.put(subBlock, tempSubBlockWork);
                }

                if (blockWork.get(block) != null) {
                    blockWork.get(block).add(task);
                } else {
                    ArrayList<String> tempBlockWork = new ArrayList<>();
                    tempBlockWork.add(task);
                    blockWork.put(block, tempBlockWork);
                }

                if (subBlock != null) {
                    if (blockSubBlock.get(block) != null) {
                        for (String str : blockSubBlock.get(block).values()) {
                            tempSubBlock.put(str, str);
                        }
                        tempSubBlock.put(subBlock, subBlock);
                        blockSubBlock.put(block, tempSubBlock);
                    } else {
                        tempSubBlock.put(subBlock, subBlock);
                        blockSubBlock.put(block, tempSubBlock);
                    }
                }

                if (block != null) {
                    if (sectorBlock.get(sector) != null) {
                        for (String str : sectorBlock.get(sector).values()) {
                            tempBlock.put(str, str);
                        }
                        tempBlock.put(block, block);
                        sectorBlock.put(sector, tempBlock);
                    } else {
                        tempBlock.put(block, block);
                        sectorBlock.put(sector, tempBlock);
                    }
                }
                //endregion

                //region Заполнение массивов данных
                if (allValuesWork.get(task + actCode) != null) {
                    allValuesWork.put(task + actCode + "pv_plan", rs.getDouble("pv_plan") + allValuesWork.get(task + actCode + "pv_plan"));
                    allValuesWork.put(task + actCode + "t_plan", rs.getDouble("t_plan") + allValuesWork.get(task + actCode + "t_plan"));
                } else {
                    allValuesWork.put(task + actCode, 0.00);
                    allValuesWork.put(task + actCode + "pv_plan", rs.getDouble("pv_plan"));
                    allValuesWork.put(task + actCode + "t_plan", rs.getDouble("t_plan"));
                }

                if (allValuesSubBlock.get(subBlock + actCode) != null) {
                    allValuesSubBlock.put(subBlock + actCode + "pv_plan", rs.getDouble("pv_plan") + allValuesSubBlock.get(subBlock + actCode + "pv_plan"));
                    allValuesSubBlock.put(subBlock + actCode + "t_plan", rs.getDouble("t_plan") + allValuesSubBlock.get(subBlock + actCode + "t_plan"));
                } else {
                    allValuesSubBlock.put(subBlock + actCode, 0.00);
                    allValuesSubBlock.put(subBlock + actCode + "pv_plan", rs.getDouble("pv_plan"));
                    allValuesSubBlock.put(subBlock + actCode + "t_plan", rs.getDouble("t_plan"));
                }

                if (allValuesBlock.get(block + actCode) != null) {
                    allValuesBlock.put(block + actCode + "pv_plan", rs.getDouble("pv_plan") + allValuesBlock.get(block + actCode + "pv_plan"));
                    allValuesBlock.put(block + actCode + "t_plan", rs.getDouble("t_plan") + allValuesBlock.get(block + actCode + "t_plan"));
                } else {
                    allValuesBlock.put(block + actCode, 0.00);
                    allValuesBlock.put(block + actCode + "pv_plan", rs.getDouble("pv_plan"));
                    allValuesBlock.put(block + actCode + "t_plan", rs.getDouble("t_plan"));
                }

                if (allValuesSector.get(sector + actCode) != null) {
                    allValuesSector.put(sector + actCode + "pv_plan", rs.getDouble("pv_plan") + allValuesSector.get(sector + actCode + "pv_plan"));
                    allValuesSector.put(sector + actCode + "t_plan", rs.getDouble("t_plan") + allValuesSector.get(sector + actCode + "t_plan"));
                } else {
                    allValuesSector.put(sector + actCode, 0.00);
                    allValuesSector.put(sector + actCode + "pv_plan", rs.getDouble("pv_plan"));
                    allValuesSector.put(sector + actCode + "t_plan", rs.getDouble("t_plan"));
                }

                if (allValuesSection.get(section + actCode) != null) {
                    allValuesSection.put(section + actCode + "pv_plan", rs.getDouble("pv_plan") + allValuesSection.get(section + actCode + "pv_plan"));
                    allValuesSection.put(section + actCode + "t_plan", rs.getDouble("t_plan") + allValuesSection.get(section + actCode + "t_plan"));
                } else {
                    allValuesSection.put(section + actCode, 0.00);
                    allValuesSection.put(section + actCode + "pv_plan", rs.getDouble("pv_plan"));
                    allValuesSection.put(section + actCode + "t_plan", rs.getDouble("t_plan"));
                }

                if (allValuesSetup.get(project + actCode) != null) {
                    allValuesSetup.put(project + actCode + "pv_plan", rs.getDouble("pv_plan") + allValuesSetup.get(project + actCode + "pv_plan"));
                    allValuesSetup.put(project + actCode + "t_plan", rs.getDouble("t_plan") + allValuesSetup.get(project + actCode + "t_plan"));
                } else {
                    allValuesSetup.put(project + actCode, 0.00);
                    allValuesSetup.put(project + actCode + "pv_plan", rs.getDouble("pv_plan"));
                    allValuesSetup.put(project + actCode + "t_plan", rs.getDouble("t_plan"));
                }
                //endregion
            }

            workType = new ArrayList<>(hashWorkType.values());
            Collections.sort(workType, new Comparator<String>() {
                public int compare(String o1, String o2) {
                    return o1.compareTo(o2);
                }
            });

            rs.close();
            stmt.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    //endregion

    //region Создание книги EXCEL, листа, запуск генерации отчёта и запись получившийся книги
    private void createReport() {
        wb = new HSSFWorkbook();
        sheet = wb.createSheet("Лист1");
        rownum = 0;

        sheet.setRowSumsBelow(false);

        createStyles();
        addTitle();

        ArrayList<String> setupList = new ArrayList<>(setup.values());
        Collections.sort(setupList, new Comparator<String>() {
            public int compare(String o1, String o2) {
                return o1.compareTo(o2);
            }
        });
        for (String setupName : setupList) {
            try {
                addSetup(setupName);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        sheet.setColumnWidth(0, 256 * 51);

        for (int i = 1; i < workType.size() * 2 + 1; i++) {
            sheet.setColumnWidth(i, 256 * 8);
        }

        FileOutputStream out;
        try {
            out = new FileOutputStream(new File(pathXLSFile + "(Расширенный отчет).xls"));
            wb.write(out);
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //endregion

    //region Добавление шапки таблицы
    private void addTitle() {
        Row row = sheet.createRow(rownum++);
        row.setHeightInPoints(147.00f);
        Cell cell;

        cell = row.createCell(0);
        cell.setCellValue("Наименование здания или сооружения");
        cell.setCellStyle(styles.get("name_build_or_setup"));
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));

        int i = 1;

        for (String rn : workType) {
            cell = row.createCell(i);
            cell.setCellValue(rn);
            cell.setCellStyle(styles.get("rn_style"));
            if (curReport == 2) {
                sheet.addMergedRegion(new CellRangeAddress(0, 0, i, i + 2));
                i += 3;
            } else {
                sheet.addMergedRegion(new CellRangeAddress(0, 0, i, i + 1));
                i += 2;
            }
        }
        cell = row.createCell(i);
        cell.setCellValue("Код работы");
        cell.setCellStyle(styles.get("rn_style"));

        Row row2 = sheet.createRow(rownum++);
        row2.setHeightInPoints(32.00f);
            for (int j = 1; j < workType.size() * 2 + 1; j++) {
                if (j % 2 == 0) {
                    cell = row2.createCell(j);
                    cell.setCellValue("Т-ты план.");
                    cell.setCellStyle(styles.get("under_rn_style"));
                } else {
                    cell = row2.createCell(j);
                    cell.setCellValue("ФО план.");
                    cell.setCellStyle(styles.get("under_rn_style"));
                }
            }

        if (type.equals("extendSmet")) {
            cell = row2.createCell(i);
            cell.setCellStyle(styles.get("under_rn_style"));
        }

    }
    //endregion

    //region Добавление стилей таблицы
    private void createStyles() {
        styles = new HashMap<>();
        CellStyle style;

        //region Цветовая палитра
        HSSFPalette palette = wb.getCustomPalette();
        palette.setColorAtIndex((short) 57, (byte) 248, (byte) 248, (byte) 248);
        palette.setColorAtIndex((short) 58, (byte) 141, (byte) 180, (byte) 226);
        palette.setColorAtIndex((short) 59, (byte) 191, (byte) 212, (byte) 190);
        palette.setColorAtIndex((short) 60, (byte) 230, (byte) 184, (byte) 183);
        palette.setColorAtIndex((short) 61, (byte) 218, (byte) 238, (byte) 243);
        palette.setColorAtIndex((short) 62, (byte) 220, (byte) 230, (byte) 241);
        palette.setColorAtIndex((short) 63, (byte) 235, (byte) 235, (byte) 235);
        //endregion

        //region Шрифты
        HSSFFont mainFont = wb.createFont();
        mainFont.setFontHeightInPoints((short) 12);
        mainFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        mainFont.setFontName("Times New Roman");
        HSSFFont blockFont = wb.createFont();
        blockFont.setFontHeightInPoints((short) 12);
        blockFont.setFontName("Times New Roman");
        HSSFFont subblockFont = wb.createFont();
        subblockFont.setFontHeightInPoints((short) 12);
        subblockFont.setFontName("Times New Roman");
        subblockFont.setItalic(true);
        HSSFFont taskFont = wb.createFont();
        taskFont.setFontHeightInPoints((short) 11);
        taskFont.setFontName("Times New Roman");
        taskFont.setItalic(true);
        HSSFFont smrFont = wb.createFont();
        smrFont.setFontHeightInPoints((short) 10);
        smrFont.setFontName("Times New Roman");
        smrFont.setItalic(true);
        //endregion

        //region Стили для ячеек

        //region name_build_or_setup - стиль ячейки 1.1 (Огромная зелёная херня)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(59).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("name_build_or_setup", style);
        //endregion

        //region rn_style - Стиль первой строки "Вид работ"
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(mainFont);
        style.setRotation((short) 90);
        style.setWrapText(true);
        style.setFillForegroundColor(palette.getColor(58).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderLeft(CellStyle.BORDER_MEDIUM);
        style.setLeftBorderColor(HSSFColor.BLACK.index);

        styles.put("rn_style", style);
        //endregion

        //region under_rn_style - Стиль второй строки значения факт/план и т.д.
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(mainFont);
        style.setWrapText(true);
        style.setFillForegroundColor(palette.getColor(62).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderLeft(CellStyle.BORDER_MEDIUM);
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        styles.put("under_rn_style", style);
        //endregion

        //region setup_left - Название установки
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setWrapText(true);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        styles.put("setup_left", style);
        //endregion

        //region setup_right_1 - первый столбец значений в строке установки
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("setup_right_1", style);
        //endregion

        //region setup_right_2 - второй/третий столбец значений в строке установки(жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(60).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("setup_right_2", style);
        //endregion

        //region sector_left - Название сектора
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setWrapText(true);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        styles.put("sector_left", style);
        //endregion

        //region sector_right_1 - первый столбец значений в строке сектора
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sector_right_1", style);
        //endregion

        //region sector_right_2 - второй/третий столбец значений в строке сектора(жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(61).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(mainFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_MEDIUM);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_MEDIUM);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sector_right_2", style);
        //endregion

        //region block_left - Название блока
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(blockFont);
        style.setWrapText(true);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        styles.put("block_left", style);
        //endregion

        //region block_right_1 - первый столбец значений в строке блока
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(blockFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("block_right_1", style);
        //endregion

        //region block_right_2 - второй/третий столбец значений в строке блока (жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(blockFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("block_right_2", style);
        //endregion

        //region sub_block_left - Название подблока
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subblockFont);
        style.setWrapText(true);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        styles.put("sub_block_left", style);
        //endregion

        //region sub_block_right_1 - первый столбец значений в строке подблока
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subblockFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sub_block_right_1", style);
        //endregion

        //region sub_block_right_2 - второй/третий столбец значений в строке подблока (жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subblockFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("sub_block_right_2", style);
        //endregion

        //region task_left - Код и название работы
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(63).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(taskFont);
        style.setWrapText(true);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        styles.put("task_left", style);
        //endregion

        //region task_right_1 - первый столбец значений в строке работы
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(63).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(taskFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("task_right_1", style);
        //endregion

        //region task_right_2 - второй/третий столбец значений в строке работы (жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(63).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(taskFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("task_right_2", style);
        //endregion

        //region smr_left - Название сметной строки
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(smrFont);
        style.setWrapText(true);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        styles.put("smr_left", style);
        //endregion

        //region smr_right_1 - первый столбец значений в строке сметной строки
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(smrFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("smr_right_1", style);
        //endregion

        //region smr_right_2 - второй/третий столбец значений в строке сметной строки (жирная правая граница)
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFillForegroundColor(palette.getColor(57).getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(smrFont);
        style.setDataFormat(wb.createDataFormat().getFormat("0"));

        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(HSSFColor.BLACK.index);

        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(HSSFColor.BLACK.index);

        style.setBorderRight(CellStyle.BORDER_MEDIUM);
        style.setRightBorderColor(HSSFColor.BLACK.index);

        styles.put("smr_right_2", style);
        //endregion

        //endregion
    }
    //endregion

    private void addSetup(String setupName) throws SQLException {
        row = sheet.createRow(rownum++);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellValue(setupName);
        cell.setCellStyle(styles.get("setup_left"));

        int i = 1;

        for (String rn : workType) {
            cell = row.createCell(i);

                if (allValuesSection.get(setupName + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesSection.get(setupName + rn + "pv_plan"));
                } else {
                    cell.setCellValue("---");
                }

            cell.setCellStyle(styles.get("setup_right_1"));
            i++;
            cell = row.createCell(i);

                if (allValuesSection.get(setupName + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesSection.get(setupName + rn + "t_plan"));
                } else {
                    cell.setCellValue("---");
                }

                cell.setCellStyle(styles.get("setup_right_2"));
            i++;
        }

        if (type.equals("extendSmet")) {
            cell = row.createCell(i);
            cell.setCellStyle(styles.get("setup_right_2"));
        }

        ArrayList<String> sectorList = new ArrayList<>(hashSector.values());
        Collections.sort(sectorList, new Comparator<String>() {
            public int compare(String o1, String o2) {
                return o1.compareTo(o2);
            }
        });

        int groupStart = rownum;
        int sectorCount = 0;
        for (String sector : sectorList) {
            addSector(sector);
            sectorCount++;
            busyDlg.setDescription("Запись " + sectorCount + " сектора из" + sectorList.size());
        }
        sheet.groupRow(groupStart, rownum);
    }

    private void addSector(String sector) throws SQLException {
        int size = 0;
        if (taskSQL.get(sector) != null) {
            size = taskSQL.get(sector).size();
        }
        createSheet(size);
        row = sheet.createRow(rownum++);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellValue(sector);
        cell.setCellStyle(styles.get("sector_left"));

        int i = 1;

        for (String rn : workType) {
            cell = row.createCell(i);

                if (allValuesSector.get(sector + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesSector.get(sector + rn + "pv_plan"));
                } else {
                    cell.setCellValue("---");
                }

            cell.setCellStyle(styles.get("sector_right_1"));
            i++;
            cell = row.createCell(i);

                if (allValuesSector.get(sector + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesSector.get(sector + rn + "t_plan"));
                } else {
                    cell.setCellValue("---");
                }
                cell.setCellStyle(styles.get("sector_right_2"));

            i++;

        }

        if (type.equals("extendSmet")) {
            cell = row.createCell(i);
            cell.setCellStyle(styles.get("sector_right_2"));
        }

        if (sectorBlock.get(sector) != null) {
            int groupStart = rownum;
            ArrayList<String> sectorBlockList = new ArrayList<>(sectorBlock.get(sector).values());
            Collections.sort(sectorBlockList, new Comparator<String>() {
                public int compare(String o1, String o2) {
                    return o1.compareTo(o2);
                }
            });

            for (String block : sectorBlockList) {
                addBlock(block);
            }
            sheet.groupRow(groupStart, rownum);
        }
    }

    private void addBlock(String block) {
        int size = 0;
        if (blockWork.get(block) != null) {
            size = blockWork.get(block).size();
        }
        createSheet(size);

        row = sheet.createRow(rownum++);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellValue(block);
        cell.setCellStyle(styles.get("block_left"));

        int i = 1;

        for (String rn : workType) {
            cell = row.createCell(i);


                if (allValuesBlock.get(block + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "pv_plan"));
                } else {
                    cell.setCellValue("---");
                }

            cell.setCellStyle(styles.get("block_right_1"));
            i++;
            cell = row.createCell(i);

                if (allValuesBlock.get(block + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesBlock.get(block + rn + "t_plan"));
                } else {
                    cell.setCellValue("---");
                }


                cell.setCellStyle(styles.get("block_right_2"));

            i++;
        }

        if (type.equals("extendSmet")) {
            cell = row.createCell(i);
            cell.setCellStyle(styles.get("block_right_2"));
        }

        if (blockSubBlock.get(block) != null) {
            int groupStart = rownum;
            ArrayList<String> blockSunBlockList = new ArrayList<>(blockSubBlock.get(block).values());
            Collections.sort(blockSunBlockList, new Comparator<String>() {
                public int compare(String o1, String o2) {
                    return o1.compareTo(o2);
                }
            });
            for (String subBlock : blockSunBlockList) {
                addSubBlock(subBlock);
            }
            sheet.groupRow(groupStart, rownum);
        } else {
            if (type.equals("extendSmet")) {
                HashMap<String, Double> blockSmet = new HashMap<>();
                HashMap<String, Double> smetSmr = new HashMap<>();
                HashMap<String, HashSet<Integer>> smetSmrList= new HashMap<>();
                HashSet<String> smetList = new HashSet<>();
                HashSet<String> smrList = new HashSet<>();
                for (String work : blockWork.get(block)) {
                    if (taskSQL.get(work) != null) {
                        for (Integer smr : taskSQL.get(work)) {
                            if (sqlList.get(smr) != null) {
                                SQLLink link = sqlList.get(smr);
                                int smrId = link.getId();
                                String smetName = link.getSmNum();
                                String smrName = link.getSmrName();
                                String actCode = taskType.get(work);
                                Double tPlan = link.getTVol().doubleValue();
                                Double pvPlan = 0.00;
                                String pvVol = hashWork.get(work).getPvCode();
                                if (link.getPv() && (pvVol.equals("PV35") || pvVol.equals("PV36") || (!pvVol.contains("PV3") && !pvVol.contains("PV4")))) {
                                    pvPlan = link.getPvVol().doubleValue();
                                }

                                smetList.add(smetName);
                                smrList.add(smrName);
                                HashSet<Integer> temp = new HashSet<>();

                                if (smr != null) {
                                    if (smetSmrList.get(smetName) != null) {
                                        for (Integer str : smetSmrList.get(smetName)) {
                                            temp.add(str);
                                        }
                                        temp.add(smrId);
                                        smetSmrList.put(smetName, temp);
                                    } else {
                                        temp.add(smrId);
                                        smetSmrList.put(smetName, temp);
                                    }
                                }

                                if (blockSmet.get(smetName + actCode) != null) {
                                    blockSmet.put(smetName + actCode + "pv_plan", pvPlan + blockSmet.get(smetName + actCode + "pv_plan"));
                                    blockSmet.put(smetName + actCode + "t_plan", tPlan + blockSmet.get(smetName + actCode + "t_plan"));
                                } else {
                                    blockSmet.put(smetName + actCode, 0.00);
                                    blockSmet.put(smetName + actCode + "pv_plan", pvPlan);
                                    blockSmet.put(smetName + actCode + "t_plan", tPlan);
                                }

                                if (smetSmr.get(smrId + actCode) != null) {
                                    smetSmr.put(smrId + actCode + "pv_plan", pvPlan + smetSmr.get(smrId + actCode + "pv_plan"));
                                    smetSmr.put(smrId + actCode + "t_plan", tPlan + smetSmr.get(smrId + actCode + "t_plan"));
                                } else {
                                    smetSmr.put(smrId + actCode, 0.00);
                                    smetSmr.put(smrId + actCode + "pv_plan", pvPlan);
                                    smetSmr.put(smrId + actCode + "t_plan", tPlan);
                                }
                            }
                        }
                    }
                }

                int groupStart = rownum;
                ArrayList<String> blockSmetList = new ArrayList<>(smetList);
                Collections.sort(blockSmetList, new Comparator<String>() {
                    public int compare(String o1, String o2) {
                        return o1.compareTo(o2);
                    }
                });
                for (String smet : blockSmetList) {
                    addSmet(smet, blockSmet, smetSmr, smetSmrList);
                }
                sheet.groupRow(groupStart, rownum);

            } else {
                if (blockWork.get(block) != null) {
                    int groupStart = rownum;
                    Collections.sort(blockWork.get(block), new Comparator<String>() {
                        public int compare(String o1, String o2) {
                            return o1.compareTo(o2);
                        }
                    });
                    for (String work : blockWork.get(block)) {
                        addWork(work);
                    }
                    sheet.groupRow(groupStart, rownum);
                }
            }
        }
    }

    private void addSubBlock(String subBlock) {
        int size = 0;
        if (taskSQL.get(subBlock) != null) {
            size = taskSQL.get(subBlock).size();
        }
        createSheet(size);
        row = sheet.createRow(rownum++);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellValue(subBlock);
        cell.setCellStyle(styles.get("sub_block_left"));

        int i = 1;

        for (String rn : workType) {
            cell = row.createCell(i);

                if (allValuesSubBlock.get(subBlock + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesSubBlock.get(subBlock + rn + "pv_plan"));
                } else {
                    cell.setCellValue("---");
                }

            cell.setCellStyle(styles.get("sub_block_right_1"));
            i++;
            cell = row.createCell(i);

                if (allValuesSubBlock.get(subBlock + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesSubBlock.get(subBlock + rn + "t_plan"));
                } else {
                    cell.setCellValue("---");
                }

                cell.setCellStyle(styles.get("sub_block_right_2"));
            i++;
        }

        if (type.equals("extendSmet")) {
            cell = row.createCell(i);
            cell.setCellStyle(styles.get("task_right_2"));

            HashMap<String, Double> subBlockSmet = new HashMap<>();
            HashMap<String, Double> smetSmr = new HashMap<>();
            HashMap<String, HashSet<Integer>> smetSmrList= new HashMap<>();
            HashSet<String> smetList = new HashSet<>();
            HashSet<String> smrList = new HashSet<>();
            for (String work : subBlockWork.get(subBlock)) {
                if (taskSQL.get(work) != null) {
                    for (Integer smr : taskSQL.get(work)) {
                        if (sqlList.get(smr) != null) {
                            SQLLink link = sqlList.get(smr);
                            int smrId = link.getId();
                            String smetName = link.getSmNum();
                            String smrName = link.getSmrName();
                            String actCode = taskType.get(work);
                            smetList.add(smetName);
                            smrList.add(smrName);
                            HashSet<Integer> temp = new HashSet<>();

                            Double tPlan = link.getTVol().doubleValue();
                            Double pvPlan = 0.00;
                            String pvVol = hashWork.get(work).getPvCode();
                            if (link.getPv() && (pvVol.equals("PV35") || pvVol.equals("PV36") || (!pvVol.contains("PV3") && !pvVol.contains("PV4")))) {
                                pvPlan = link.getPvVol().doubleValue();
                            }

                            if (smr != null) {
                                if (smetSmrList.get(smetName) != null) {
                                    for (Integer str : smetSmrList.get(smetName)) {
                                        temp.add(str);
                                    }
                                    temp.add(smrId);
                                    smetSmrList.put(smetName, temp);
                                } else {
                                    temp.add(smrId);
                                    smetSmrList.put(smetName, temp);
                                }
                            }

                            if (subBlockSmet.get(smetName + actCode) != null) {
                                subBlockSmet.put(smetName + actCode + "pv_plan", pvPlan + subBlockSmet.get(smetName + actCode + "pv_plan"));
                                subBlockSmet.put(smetName + actCode + "t_plan", tPlan + subBlockSmet.get(smetName + actCode + "t_plan"));
                            } else {
                                subBlockSmet.put(smetName + actCode, 0.00);
                                subBlockSmet.put(smetName + actCode + "pv_plan", pvPlan);
                                subBlockSmet.put(smetName + actCode + "t_plan", tPlan);
                            }

                            if (smetSmr.get(smrId + actCode) != null) {
                                smetSmr.put(smrId + actCode + "pv_plan", pvPlan + smetSmr.get(smrId + actCode + "pv_plan"));
                                smetSmr.put(smrId + actCode + "t_plan", tPlan + smetSmr.get(smrId + actCode + "t_plan"));
                            } else {
                                smetSmr.put(smrId + actCode, 0.00);
                                smetSmr.put(smrId + actCode + "pv_plan", pvPlan);
                                smetSmr.put(smrId + actCode + "t_plan", tPlan);
                            }
                        }
                    }
                }
            }

            int groupStart = rownum;
            ArrayList<String> subBlockSmetList = new ArrayList<>(smetList);
            Collections.sort(subBlockSmetList, new Comparator<String>() {
                public int compare(String o1, String o2) {
                    return o1.compareTo(o2);
                }
            });
            for (String smet : subBlockSmetList) {
                addSmet(smet, subBlockSmet, smetSmr, smetSmrList);
            }
            sheet.groupRow(groupStart, rownum);
        } else {
            if (subBlockWork.get(subBlock) != null) {
                int groupStart = rownum;
                ArrayList<String> subBlockWorkList = new ArrayList<>(subBlockWork.get(subBlock));
                Collections.sort(subBlockWorkList, new Comparator<String>() {
                    public int compare(String o1, String o2) {
                        return o1.compareTo(o2);
                    }
                });
                for (String work : subBlockWorkList) {
                    addWork(work);
                }
                sheet.groupRow(groupStart, rownum);
            }
        }
    }

    private void addWork(String work) {
        int size = 0;
        if (taskSQL.get(work) != null) {
            size = taskSQL.get(work).size();
        }
        createSheet(size);
        String oneRn = new String();
        row = sheet.createRow(rownum++);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellValue(hashWork.get(work).getTaskCode() + " - " + hashWork.get(work).getTaskName());
        cell.setCellStyle(styles.get("task_left"));

        int i = 1;

        for (String rn : workType) {
            cell = row.createCell(i);

                if (allValuesWork.get(work + rn + "pv_plan") != null) {
                    cell.setCellValue(allValuesWork.get(work + rn + "pv_plan"));
                    oneRn = rn;
                } else {
                    cell.setCellValue("---");
                }


            cell.setCellStyle(styles.get("task_right_1"));
            i++;
            cell = row.createCell(i);

                if (allValuesWork.get(work + rn + "t_plan") != null) {
                    cell.setCellValue(allValuesWork.get(work + rn + "t_plan"));
                } else {
                    cell.setCellValue("---");
                }
                cell.setCellStyle(styles.get("task_right_2"));

            i++;
        }

        if (taskSQL.get(work) != null) {
            int groupStart = rownum;
            Collections.sort(taskSQL.get(work), new Comparator<Integer>() {

                @Override
                public int compare(Integer integer, Integer integer2) {
                    if (integer < integer2) {
                        return -1;
                    } else if (integer > integer2) {
                        return 1;
                    }
                    return 0;
                }
            });
            for (Integer task : taskSQL.get(work)) {
                addSMR(task, oneRn);
            }
            sheet.groupRow(groupStart, rownum);
        }
    }

    private void addSmet(String smet, HashMap<String, Double> blockSmet, HashMap<String, Double> smetSmr, HashMap<String, HashSet<Integer>> smetSmrList) {
//        int size = 0;
//        if (blockSmet.get(block) != null) {
//            size = blockSmet.get(block).size();
//        }
//        createSheet(size);

        row = sheet.createRow(rownum++);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellValue(smet);
        cell.setCellStyle(styles.get("task_left"));

        int i = 1;

        for (String rn : workType) {
            cell = row.createCell(i);


            if (blockSmet.get(smet + rn + "pv_plan") != null) {
                cell.setCellValue(blockSmet.get(smet + rn + "pv_plan"));
            } else {
                cell.setCellValue("---");
            }

            cell.setCellStyle(styles.get("task_right_1"));
            i++;
            cell = row.createCell(i);

            if (blockSmet.get(smet + rn + "t_plan") != null) {
                cell.setCellValue(blockSmet.get(smet + rn + "t_plan"));
            } else {
                cell.setCellValue("---");
            }


            cell.setCellStyle(styles.get("task_right_2"));

            i++;
        }

        if (type.equals("extendSmet")) {
            cell = row.createCell(i);
            cell.setCellStyle(styles.get("task_right_2"));
        }

        if (smetSmrList.get(smet) != null) {
            int groupStart = rownum;

            ArrayList<Smrka> smrkas = new ArrayList<>();
            for (int smrr : smetSmrList.get(smet)) {
                smrkas.add(new Smrka(smrr, Integer.parseInt(sqlList.get(smrr).getSmrNum())));
            }

            Collections.sort(smrkas, new Comparator<Smrka>() {
                @Override
                public int compare(Smrka smrka, Smrka smrka2) {
                    int result = 0;
                    if (smrka.getNum() < smrka2.getNum()) {
                        result = -1;
                    } else if (smrka.getNum() > smrka2.getNum()) {
                        result = 1;
                    } else if (smrka.getNum() == smrka2.getNum()) {
                        result = 0;
                    }
                    return result;
                }
            });


            for (Smrka smr : smrkas) {
                addSmrSmet(smr.getId(), smetSmr);
            }
            sheet.groupRow(groupStart, rownum);
        }
    }

    private void addSmrSmet(Integer smr, HashMap<String, Double> smetSmr) {
//        int size = 0;
//        if (blockSmet.get(block) != null) {
//            size = blockSmet.get(block).size();
//        }
//        createSheet(size);

        row = sheet.createRow(rownum++);
        Cell cell;
        cell = row.createCell(0);
        cell.setCellValue(sqlList.get(smr).getSmrNum() + ". " + sqlList.get(smr).getSmrName());
        cell.setCellStyle(styles.get("smr_left"));

        int i = 1;

        for (String rn : workType) {
            cell = row.createCell(i);


            if (smetSmr.get(smr + rn + "pv_plan") != null) {
                cell.setCellValue(smetSmr.get(smr + rn + "pv_plan"));
            } else {
                cell.setCellValue("---");
            }

            cell.setCellStyle(styles.get("smr_right_1"));
            i++;
            cell = row.createCell(i);

            if (smetSmr.get(smr + rn + "t_plan") != null) {
                cell.setCellValue(smetSmr.get(smr + rn + "t_plan"));
            } else {
                cell.setCellValue("---");
            }

            cell.setCellStyle(styles.get("smr_right_2"));

            i++;
        }
        cell = row.createCell(i);
        cell.setCellStyle(styles.get("smr_right_2"));
        cell.setCellValue(sqlList.get(smr).getTaskShortName());

    }

    private void addSMR(Integer smr, String oneRn) {
        createSheet(0);
        row = sheet.createRow(rownum++);
        cell = row.createCell(0);
        cell.setCellValue(sqlList.get(smr).getSmrName());
        cell.setCellStyle(styles.get("smr_left"));

        int i = 1;

        for (String rn : workType) {
            cell = row.createCell(i);

                if (rn.equals(oneRn)) {
                    cell.setCellValue(sqlList.get(smr).getPvVol().doubleValue());
                } else {
                    cell.setCellValue("---");
                }

            cell.setCellStyle(styles.get("smr_right_1"));
            i++;
            cell = row.createCell(i);

                if (rn.equals(oneRn)) {
                    cell.setCellValue(sqlList.get(smr).getTVol().doubleValue());
                } else {
                    cell.setCellValue("---");
                }
            cell.setCellStyle(styles.get("smr_right_2"));
            i++;
        }
    }

    private void toHash() {
        sqlList = new HashMap<>();
        taskSQL = new HashMap<>();
        for (SQLLink sql : linkArray) {
            sqlList.put(sql.getId(), sql);
            if (taskSQL.get(sql.getTaskShortName()) != null) {
                taskSQL.get(sql.getTaskShortName()).add(sql.getId());
            } else {
                ArrayList tempSet = new ArrayList();
                tempSet.add(sql.getId());
                taskSQL.put(sql.getTaskShortName(), tempSet);
            }
        }
    }

    private void createSheet(int size) {
        if (rownum + size > 65534) {
            rownum = 0;
            sheet.setColumnWidth(0, 256 * 51);

            for (int i = 1; i < workType.size() * 2 + 1; i++) {
                sheet.setColumnWidth(i, 256 * 8);
            }
            sheet = wb.createSheet("Лист" + sheetCount++ + "(Продолжение)");
            sheet.setRowSumsBelow(false);
            sheet.setColumnWidth(0, 256 * 51);

            for (int i = 1; i < workType.size() * 2 + 1; i++) {
                sheet.setColumnWidth(i, 256 * 8);
            }
            addTitle();
        }
    }
    class Smrka {
        private int id, num;

        public Smrka(int id, int num) {
            this.id = id;
            this.num = num;
        }

        public int getId() {
            return id;
        }

        public int getNum() {
            return num;
        }
    }

    class Taska {
        private String taskName, taskCode, pvCode;

        Taska(String taskName, String taskCode, String pvCode) {
            this.taskName = taskName;
            this.taskCode = taskCode;
            this.pvCode = pvCode;
        }

        public String getTaskName() {
            return taskName;
        }

        public void setTaskName(String taskName) {
            this.taskName = taskName;
        }

        public String getTaskCode() {
            return taskCode;
        }

        public void setTaskCode(String taskCode) {
            this.taskCode = taskCode;
        }

        public String getPvCode() {
            return pvCode;
        }

        public void setPvCode(String pvCode) {
            this.pvCode = pvCode;
        }
    }
}

